package com.founder.syn.dao.imp;

import java.util.LinkedHashMap;
import java.util.Map;

import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.founder.syn.config.SynInfoConfig;
import com.founder.syn.dao.SynDao;
import com.founder.syn.entity.Table;
import com.founder.syn.log.LoggerUtil;

/**
 * @author 作者 E-mail:
 * @date 创建时间：2018年1月26日 下午1:58:33
 * @version 1.0
 * @parameter
 * @since
 * @return
 */
@Component
public class SynDaoImpl extends LoggerUtil implements SynDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private SynInfoConfig synInfoConfig;

	// @Transactional(rollbackFor=Exception.class,propagation=Propagation.REQUIRES_NEW)
	@Transactional
	public void delete(Table table) throws Exception {
		/*
		 * delete from TC_USER owner where not exists (select name from
		 * T_JZ_USER outer where outer.login_name = phone)
		 */
		String owner = table.getOwner();
		String outer = table.getOuter();

		StringBuffer sb = new StringBuffer("delete from ");
		sb.append(owner);
		sb.append(" owner_table where ");

		/*
		 * 扩展 where 条件 and owner.name = 'xc' 预留 现在不做处理
		 */

		sb.append(" not exists (select ");
		Map<String, String> map = table.getSyn();
		String selectValue = MapKeyToString(map, "outer_table.", "", ",", false);
		sb.append(selectValue);
		sb.append(" from ");
		sb.append(outer);
		sb.append(" outer_table where ");
		String whereValue = MapKeyValueToString(map, "outer_table.", "owner_table.", " and ", " = ", true);
		sb.append(whereValue);
		sb.append(")");
		int i = jdbcTemplate.update(sb.toString());

		StringBuffer loginfo = new StringBuffer();
		loginfo.append("表：");
		loginfo.append(owner);
		loginfo.append("—— 成功删除的总条数：" + i);
		LOG.info(loginfo.toString());
	}

	@Transactional
	public void replace(Table table) throws Exception {
		/*
		 * update tc_user set nickname = (select u.name from T_JZ_USER u WHERE
		 * u.login_name = phone ) where exists (select name from T_JZ_USER u
		 * where u.login_name = phone)
		 */
		String owner = table.getOwner();
		String outer = table.getOuter();
		Map syn = table.getSyn();
		Map<String, String> replace = table.getReplace();
		for (Map.Entry<String, String> entry : replace.entrySet()) {
			StringBuffer sb = new StringBuffer("update ");
			sb.append(owner);
			sb.append(" owner_table set owner_table.");
			sb.append(entry.getKey());
			sb.append(" = ( select outer_table.");
			sb.append(entry.getValue());
			sb.append(" from ");
			sb.append(outer);
			sb.append(" outer_table where ");
			/*
			 * 扩展 where 条件 and outer.name = 'xc' 预留 现在不做处理
			 */

			String where = MapKeyValueToString(syn, "outer_table.", "owner_table.", " and ", " = ", true);

			String exists = where;
			sb.append(where);
			sb.append(" ) ");
			sb.append(" where ");
			/*
			 * 扩展 where 条件 and owner.name = 'xc' 预留 现在不做处理
			 */

			/*
			 * 以下情况 not 必须删 如果自己的表多了的话 就可能会出现错误
			 */
			 
				sb.append(" not exists (");
			 
			/*
			 * 排除已经替换的数据
			 */
			String replacewhere = "";
			if (replace != null) {
				replacewhere = MapKeyValueToString(replace, "outer_table.", "owner_table.", " and ", " = ", true);
			}
			if (!StringUtils.isEmpty(replacewhere)) {
				where += " and " + replacewhere;
			}
			sb.append("select 1 from ");
			sb.append(outer);
			sb.append(" outer_table where ");
			sb.append(where);
			sb.append(" ) ");
			
			/*
			 * 只更新自己的表
			 */
			sb.append(" and  ");
			sb.append(" exists (");
			 
			sb.append("select 1 from ");
			sb.append(outer);
			sb.append(" outer_table where ");
			sb.append(exists);
			sb.append(" ) ");

			int i = jdbcTemplate.update(sb.toString());
			StringBuffer loginfo = new StringBuffer();
			loginfo.append("表：");
			loginfo.append(owner);
			loginfo.append("——成功更新的字段：");
			loginfo.append(entry.getKey());
			loginfo.append("——总条数：" + i);
			LOG.info(loginfo.toString());

		}

	}

	public void insert(Table table) throws Exception {
		/*
		 * insert into TC_TEAM(team_id,team_name,pid,create_user_id,top_team_id,
		 * User_Car_Status) select code as team_id, name as team_name,upcode as
		 * pid,'admin' as create_user_id,'341600000000' as top_team_id,'0' as
		 * user_car_status from t_jz_zzjg where id
		 * ='2EDE9E7B6C474B95E050007F01006738'
		 * 
		 * and exists (select team_id from TC_TEAM u where team_id = code)
		 */
		String owner = table.getOwner();
		String outer = table.getOuter();
		Map<String, String> syn = table.getSyn();
		Map<String, String> defaultValue = table.getDefaultValue();
		Map<String, String> insert = table.getInsert();

		String bufferSyn = MapKeyToString(syn, "", "", ",", true);
		String bufferInsert = "";
		if (insert != null) {
			bufferInsert = MapKeyToString(insert, "", "", ",", true);
		}
		String bufferDefault = "";
		if (defaultValue != null) {
			bufferDefault = MapKeyToString(defaultValue, "", "", ",", true);
		}
		String bufferColumns = bufferSyn;
		if (!StringUtils.isEmpty(bufferDefault)) {
			bufferColumns += "," + bufferDefault;
		}
		if (!StringUtils.isEmpty(bufferInsert)) {
			bufferColumns += "," + bufferInsert;
		}

		String selectSynBuffer = MapKeyValueToString(syn, "", "", ",", " as ", false);

		String selectDefaultBuffer = "";
		if (defaultValue != null) {
			selectDefaultBuffer = MapKeyValueToString(defaultValue, "", "", ",", " as ", false);
		}

		String selectInsertBuffer = "";
		if (insert != null) {
			selectInsertBuffer = MapKeyValueToString(insert, "", "", ",", " as ", false);
		}
		String selectColumns = selectSynBuffer;
		if (!StringUtils.isEmpty(selectDefaultBuffer)) {
			selectColumns += "," + selectDefaultBuffer;
		}
		if (!StringUtils.isEmpty(selectInsertBuffer)) {
			selectColumns += "," + selectInsertBuffer;
		}

		StringBuffer sb = new StringBuffer("insert into ");
		sb.append(owner);
		sb.append("(");
		sb.append(bufferColumns);
		sb.append(") select ");
		sb.append(selectColumns);
		sb.append(" from ");
		sb.append(table.getOuter());
		sb.append(" outer_table where ");
		/*
		 * 扩展 where 条件 and outer.name = 'xc' 预留 现在不做处理
		 */

		sb.append(" not exists (select ");
		sb.append(bufferSyn);
		sb.append(" from ");
		sb.append(owner);
		sb.append(" owner_table where ");

		String whereSynBuffer = MapKeyValueToString(syn, "outer_table.", "owner_table.", " and ", " = ", true);
		sb.append(whereSynBuffer);

		// 结束标记
		sb.append(")");
		int i = jdbcTemplate.update(sb.toString());
		StringBuffer loginfo = new StringBuffer();
		loginfo.append("表：");
		loginfo.append(owner);
		loginfo.append("——添加成功总条数：" + i);
		LOG.info(loginfo.toString());

	}

	public void delete(String sql) {
		int i = jdbcTemplate.update(sql);
	}

	/*
	 * 如果自己的表多余外界的表就会出现多余的记录
	 */
	private int checkReplace(Table table) {
		/*
		 * 
		 * select (select (cnt - count(phone)) cnt from TC_USER) eq from (select
		 * (case when cnt = 0 then -1 else cnt end) cnt from (select
		 * count(outer_table.login_name) cnt from T_JZ_USER outer_table, TC_USER
		 * owner_table where outer_table.login_name = owner_table.phone) a) a
		 * 
		 * 
		 */
		String owner = table.getOwner();
		String outer = table.getOuter();
		Map<String, String> map = table.getSyn();
		StringBuffer sb = new StringBuffer("where ");

		Map.Entry<String, String> count = map.entrySet().iterator().next();
		String k = count.getKey();
		String v = count.getValue();
		sb.append(MapKeyValueToString(map, "outer_table.", "owner_table.", " and ", " = ", false));

		StringBuffer sql = new StringBuffer("select (select (cnt-count(");
		sql.append(k);
		sql.append(")) cnt from ");
		sql.append(owner);
		sql.append(") eq   from  ( select (case when cnt = 0 then -1 else cnt end) cnt from(select count(outer_table.");
		sql.append(v);
		sql.append(") cnt  from  ");
		sql.append(outer);
		sql.append(" outer_table,");
		sql.append(owner);
		sql.append(" owner_table ");
		sql.append(sb);
		sql.append(" ) a)a");

		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);

	}

	@Override
	public int select(Table table) {
		/*
		 * select (select (cnt - count(pid)) cnt from tc_team) eq from ( select
		 * (case when cnt = '0' then '-1' end) cnt from (select count(owner.pid)
		 * cnt from T_JZ_ZZJG outer, tc_team owner where outer.upcode =
		 * owner.pid and outer.code = owner.team_id and outer.name =
		 * owner.team_name )) a
		 * 
		 */
		String owner = table.getOwner();
		String outer = table.getOuter();
		Map<String, String> map = table.getSyn();
		StringBuffer sb = new StringBuffer("where ");

		Map.Entry<String, String> count = map.entrySet().iterator().next();
		String id = count.getValue();

		sb.append(MapKeyValueToString(map, "outer_table.", "owner_table.", " and ", " = ", false));

		StringBuffer sql = new StringBuffer("select (select (cnt-count(");
		sql.append(id);
		sql.append(")) cnt from ");
		sql.append(outer);
		sql.append(") eq   from  ( select (case when cnt = 0 then -1 else cnt end) cnt from(select count(outer_table.");
		sql.append(id);
		sql.append(") cnt  from  ");
		sql.append(outer);
		sql.append(" outer_table,");
		sql.append(owner);
		sql.append(" owner_table ");
		sql.append(sb);
		sql.append(" ) a)a");

		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);

	}

	/**
	 * @Title: MapKeyToString
	 * @Description: (map的key toString方法)
	 * @param map
	 *            集合
	 * 
	 * @param prefix
	 *            key的前缀
	 * 
	 * @param suffix
	 *            key的后缀
	 * 
	 * @param split
	 *            key的分割字符
	 * 
	 * @param flag
	 *            true 是key fasle value在前 JZDataSynchronous
	 * 
	 * @return String 返回类型
	 * 
	 */
	public String MapKeyToString(Map<String, String> map, String prefix, String suffix, String split, boolean flag) {
		StringBuffer sb = new StringBuffer();

		if (flag) {

			for (Map.Entry<String, String> entry : map.entrySet()) {

				sb.append(prefix);
				sb.append(entry.getKey());
				sb.append(suffix);
				sb.append(split);
			}
		} else {
			for (Map.Entry<String, String> entry : map.entrySet()) {

				sb.append(prefix);
				sb.append(entry.getValue());
				sb.append(suffix);
				sb.append(split);
			}
		}
		if (sb.length() > 0) {

			return sb.substring(0, sb.length() - split.length());
		}
		return sb.toString();
	}

	/**
	 * @Title: MapKeyValueToString
	 * @Description: (这里用一句话描述这个方法的作用)
	 * @param map
	 *            集合
	 * @param valuePrefix
	 *            值的前缀
	 * 
	 * @param keyPrefix
	 *            key的前缀
	 * 
	 * @param split
	 *            分割符
	 * 
	 * @param connector
	 *            连接符
	 * 
	 * @param flag
	 *            true 是key 在前 fasle value在前
	 * 
	 * @param @return
	 *            JZDataSynchronous
	 * @return String 返回类型
	 * 
	 */
	public String MapKeyValueToString(Map<String, String> map, String valuePrefix, String keyPrefix, String split,
			String connector, boolean flag) {
		StringBuffer sb = new StringBuffer();

		if (flag) {
			for (Map.Entry<String, String> entry : map.entrySet()) {
				sb.append(keyPrefix);
				sb.append(entry.getKey());
				sb.append(connector);
				sb.append(valuePrefix);
				sb.append(entry.getValue());
				sb.append(split);
			}

		} else {
			for (Map.Entry<String, String> entry : map.entrySet()) {

				sb.append(valuePrefix);
				sb.append(entry.getValue());
				sb.append(connector);
				sb.append(keyPrefix);
				sb.append(entry.getKey());
				sb.append(split);
			}
		}
		if (sb.length() > 0) {
			return sb.substring(0, sb.length() - split.length());
		}
		return sb.toString();
	}

	@Test
	public void testInsert() throws Exception {
		Table table = new Table();
		table.setOwner("TC_USER");
		table.setOuter("T_JZ_USER");
		table.setMethod("delete-insert");

		Map<String, String> syn = new LinkedHashMap();
		syn.put("phone", "login_name");

		table.setSyn(syn);

		Map<String, String> defaultMap = new LinkedHashMap();
		defaultMap.put("gender", "1");
		defaultMap.put("driver_status", "0");
		defaultMap.put("activated", "1");
		defaultMap.put("password", "E10ADC3949BA59ABBE56E057F20F883E");

		Map<String, String> insert = new LinkedHashMap();
		insert.put("user_id", "id");

		table.setDefaultValue(defaultMap);
		table.setInsert(insert);
		insert(table);
	}

	@Test
	public void testSelect() throws Exception {
		Table table = new Table();
		table.setOwner("tc_team");
		table.setOuter("T_JZ_ZZJG");
		table.setMethod("delete-insert");

		Map<String, String> syn = new LinkedHashMap();
		syn.put("team_id", "code");
		syn.put("team_name", "name");
		syn.put("pid", "upcode");

		table.setSyn(syn);

		Map<String, String> defaultMap = new LinkedHashMap();
		defaultMap.put("create_user_id", "admin");
		defaultMap.put("user_car_status", "0");
		defaultMap.put("top_team_id", "341600000000");

		table.setDefaultValue(defaultMap);
		// select(table);
		delete(table);
	}

	@Test
	public void testReplace() throws Exception {
		Table table = new Table();
		table.setOwner("tc_team");
		table.setOuter("T_JZ_ZZJG");
		table.setMethod("delete-insert");

		Map<String, String> syn = new LinkedHashMap();
		syn.put("team_id", "code");
		// syn.put("team_name", "name");
		// syn.put("pid", "upcode");

		table.setSyn(syn);

		/*
		 * Map<String, String> defaultMap = new LinkedHashMap();
		 * defaultMap.put("create_user_id", "admin");
		 * defaultMap.put("user_car_status", "0"); defaultMap.put("top_team_id",
		 * "341600000000");
		 */
		Map<String, String> replaceMap = new LinkedHashMap();
		replaceMap.put("team_name", "name");
		replaceMap.put("pid", "upcode");
		// replaceMap.put("top_team_id", "341600000000");

		table.setReplace(replaceMap);
		// select(table);
		replace(table);
	}

	@Test
	public void str() {
		String s = "1234,";
		System.out.println(s.substring(0, s.length() - 1));
		;

	}

	@Test
	public void TABLE() {
		Table table = new Table();

		Map<String, String> defaultMap = new LinkedHashMap();
		defaultMap.put("create_user_id", "admin");
		defaultMap.put("user_car_status", "0");
		defaultMap.put("top_team_id", "341600000000");
		table.setDefaultValue(defaultMap);

	}

}
